package com.ykt.common.utils;

import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

import com.jfinal.plugin.activerecord.Model;

public class ExcelExportUtil {

	/**
	 * @param response
	 * @param request
	 * @param filename
	 *            导出的文件名
	 * @param titles
	 *            标题列和列名的对应.column:列名,title标题名
	 * @param records
	 *            记录
	 */
	@SuppressWarnings("unchecked")
	public static <M extends Model<M>> void exportByRecord(
			HttpServletResponse response, HttpServletRequest request,
			String filename, List<Pair> titles, List<M> records, Map<String, String> exportCount) {
		exportByRecord(response, request, filename, exportCount, new SheetData<M>(titles,
				records));
	}

	/**
	 * @param response
	 * @param request
	 * @param filename
	 *            导出的文件名
	 * @param sheetDatas
	 *            产生一个sheet需要的数据
	 */
	public static <M extends Model<M>> void exportByRecord(
			HttpServletResponse response, HttpServletRequest request,
			String filename, Map<String, String> exportCount, SheetData<M>... sheetDatas) {

		HSSFWorkbook wb = new HSSFWorkbook();
		
		// 标题行的style
		CellStyle titleCellStyle = wb.createCellStyle();
		titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER); // 居中
		titleCellStyle.setWrapText(true); // 自动换行
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 加粗
		font.setFontName("微软雅黑");
		titleCellStyle.setFont(font);
		
		titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		// 内容行的style
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
		cellStyle.setWrapText(true);
		Font font2 = wb.createFont();
		font2.setFontName("微软雅黑");
		cellStyle.setFont(font2);
		
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

		// 多张sheet
		for (SheetData<M> sheetData : sheetDatas) {
			List<Pair> titles = sheetData.titles;

			List<M> records = sheetData.records;

			HSSFSheet sheet = wb.createSheet();
			sheet.setColumnWidth(0, 10*256);

			int rowIndex = 1, cellIndex = 0;

			HSSFRow row = null;
			HSSFCell cell = null;
			
			if(exportCount !=null && exportCount.size()>0){
				// 创建统计行
				row = sheet.createRow(0);
				sheet.addMergedRegion(new Region(0,(short)0,0,(short)1));
				row.setHeight((short) 450);
				cell = row.createCell(0);
				cell.setCellStyle(titleCellStyle); // 设置样式
				cell.setCellValue("总订单数："+exportCount.get("countNum")+" 个");
				sheet.addMergedRegion(new Region(0,(short)2,0,(short)3));
				cell = row.createCell(2);
				cell.setCellStyle(titleCellStyle); // 设置样式
				cell.setCellValue("总金额："+exportCount.get("countSum")+" 元");
				sheet.addMergedRegion(new Region(0,(short)4,0,(short)5));
				cell = row.createCell(4);
				cell.setCellStyle(titleCellStyle); // 设置样式
				cell.setCellValue("实付总金额："+exportCount.get("countPaySum")+" 元");
			}


			// 创建标题行
			row = sheet.createRow(rowIndex);
			row.setHeight((short) 450);
			rowIndex++;
			
			cell = row.createCell(0);
			cell.setCellStyle(cellStyle); // 设置样式
			cell.setCellValue("编号");
			cellIndex = 1;
			
			for (Pair pair : titles) {
				cell = row.createCell(cellIndex);
				cell.setCellStyle(titleCellStyle); // 设置样式
				cellIndex++;

				cell.setCellValue(pair.title);
				cell.setCellStyle(cellStyle); // 设置样式
				sheet.setColumnWidth(cellIndex, 20*256);
			}

			int rowno = 0;
			// 处理每一行
			for (M record : records) {
				row = sheet.createRow(rowIndex);
				row.setHeight((short) 450);
				
				rowIndex++;
				rowno++;
				cell = row.createCell(0);
				cell.setCellStyle(cellStyle); // 设置样式
				cell.setCellValue(rowno);
				
				cellIndex = 1;

				for (Pair pair : titles) {
					cell = row.createCell(cellIndex);
					cell.setCellStyle(cellStyle); // 设置样式
					cellIndex++;

					Object value = record.get(pair.column);
					if (value != null) {
						if(pair.column.endsWith("orderDetails")){
							float hieght=getExcelCellAutoHeight(value.toString()+"", 8f);     
							//根据字符串的长度设置高度
							sheet.setColumnWidth(cellIndex-1, 30*256);
							sheet.setColumnWidth(cellIndex-2, 40*256);
							sheet.getRow(sheet.getLastRowNum()).setHeightInPoints(hieght); 
						}
						
						if(pair.column.equals("createTime")){
							SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
							String createTime = sdf.format(value);
							cell.setCellValue(createTime);
						}else if(pair.column.equals("amount")){
							double amount = Double.valueOf(value.toString());
							cell.setCellValue(amount);
						}else if(pair.column.equals("status")){
							String statusStr = "";
							if(value.toString().equals("MERCHANT_UNCONFIRMED")){
								statusStr = "待商户确认";
				      		}else if(value.toString().equals("USER_UNPAYMENT")){
				      			statusStr = "待用户支付";
				      		}else if(value.toString().equals("MERCHANT_UNDELIVERY")){
				      			statusStr = "待商户发货";
				      		}else if(value.toString().equals("USER_UNRECEIPT")){
				      			statusStr = "待用户收货";
				      		}else if(value.toString().equals("COMPLETED")){
				      			statusStr = "已完成";
				      		}
							cell.setCellValue(statusStr);
						}else{
							cell.setCellValue(value.toString());
						}
					}
				}
			}
		}

		// 序号
		try {
			writeStream(filename, wb, response, request);
		} catch (IOException e) {
			e.getMessage();
		}
	}

	/**
	 * 写到输出流
	 * @throws IOException 
	 */
	private static void writeStream(String filename, HSSFWorkbook wb,
			HttpServletResponse response, HttpServletRequest request) throws IOException {

		OutputStream outputStream = null;
		try {
			String agent = request.getHeader("USER-AGENT");

			filename += ".xls";
			filename.replaceAll("/", "-");
			// filename = new String(filename.getBytes("gbk"),"ISO8859_1");
			
			if (agent.toLowerCase().indexOf("firefox") > 0) {
				filename = new String(filename.getBytes("utf-8"), "iso-8859-1");
			} else {
				filename = URLEncoder.encode(filename, "UTF-8");
			}

			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + filename);
			response.setContentType("application/octet-stream;charset=UTF-8");
			outputStream = new BufferedOutputStream(response.getOutputStream());
			wb.write(outputStream);
		} catch (FileNotFoundException e) {
			e.getMessage();
		} catch (IOException e) {
			e.getMessage();
		} finally {
			outputStream.flush();
			outputStream.close();
		}

	}

	/**
	 * 标题列和列名的对应
	 */
	public static class Pair {
		public String column;
		public String title;

		public Pair(String column, String title) {
			this.column = column;
			this.title = title;
		}
	}

	/**
	 * 创建一个sheet需要的数据
	 */
	public static class SheetData<M extends Model<M>> {
		public List<Pair> titles;
		public List<M> records;

		public SheetData(List<Pair> titles, List<M> records) {
			this.titles = titles;
			this.records = records;
		}
	}
	
	/**
	 * 设置自动调整高度
	 * @param str
	 * @param fontCountInline
	 * @return
	 */
	public static float getExcelCellAutoHeight(String str, float fontCountInline) {
		float defaultRowHeight = 12.00f;//每一行的高度指定
		float defaultCount = 0.00f;
		for (int i = 0; i < str.length(); i++) {
			float ff = getregex(str.substring(i, i + 1));
			defaultCount = defaultCount + ff;
		}
		return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
	}

	public static float getregex(String charStr) {

		if(charStr==" ")
		{
			return 0.5f;
		}
		// 判断是否为字母或字符
		if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
			return 0.5f;
		}
		// 判断是否为全角

		if (Pattern.compile("[u4e00-u9fa5]+$").matcher(charStr).matches()) {
			return 1.00f;
		}
		//全角符号 及中文
		if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
			return 1.00f;
		}
		return 0.5f;

	}

}
